﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.SqlClient;
using Empresa.Sistema.Datos.Entidades;

namespace Empresa.Sistema.Datos
{
    public class Prestamos
    {

        private string ConnectionString = "Data Source=.;Initial Catalog=SistemaBibliotecaV1;Integrated Security=True";
        public int Adicionar(Prestamo pre)
        {
            try
            {
                using (var cnx = new SqlConnection(ConnectionString))
                {
                    cnx.Open();
                    string cmdText = "usp_InsertarPrestamo";
                    using (var cmd = new SqlCommand(cmdText, cnx))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Parameters.Add(new SqlParameter("@Codigo", pre.Codigo));
                        cmd.Parameters.Add(new SqlParameter("@Estado", pre.Estado));
                        cmd.Parameters.Add(new SqlParameter("@Fecha", pre.Fecha));
                        cmd.Parameters.Add(new SqlParameter("@Persona", pre.Persona));
                        cmd.Parameters.Add(new SqlParameter("@Usuario", pre.Usuario));
                        cmd.ExecuteNonQuery();

                        return (int)cmd.Parameters["@Id"].Value;
                    }
                }
            }
            catch (SqlException ex)
            {
                return -1;
            }

        }

        public void Modificar(Prestamo pre)
        {
            try
            {
                using (var cnx = new SqlConnection(ConnectionString))
                {
                    cnx.Open();
                    string cmdText = "usp_ModificarPrestamo";
                    using (var cmd = new SqlCommand(cmdText, cnx))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Parameters.Add(new SqlParameter("@Id", pre.Id));
                        cmd.Parameters.Add(new SqlParameter("@Codigo", pre.Codigo));
                        cmd.Parameters.Add(new SqlParameter("@Estado", pre.Estado));
                        cmd.Parameters.Add(new SqlParameter("@Fecha", pre.Fecha));
                        cmd.Parameters.Add(new SqlParameter("@Persona", pre.Persona));
                        cmd.Parameters.Add(new SqlParameter("@Usuario", pre.Usuario));
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException ex)
            {

            }

        }

        public void Eliminar(Prestamo pre)
        {

            try
            {
                using (var cnx = new SqlConnection(ConnectionString))
                {
                    cnx.Open();
                    string cmdText = "usp_EliminarPrestamo";
                    using (var cmd = new SqlCommand(cmdText, cnx))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Parameters.Add(new SqlParameter("@Id", pre.Id));
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException ex)
            {

            }

        }

        public List<Prestamo> Listar()
        {
            List<Prestamo> lista = new List<Prestamo>();

            try
            {
                using (var cnx = new SqlConnection(ConnectionString))
                {
                    cnx.Open();
                    string cmdText = "usp_ListarPrestamo";
                    using (var cmd = new SqlCommand(cmdText, cnx))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        SqlDataReader rdr = cmd.ExecuteReader();
                        while (rdr.Read())
                        {
                            var pre = new Prestamo();
                            pre.Id = rdr.GetInt32(rdr.GetOrdinal("Id"));
                            pre.Codigo = rdr.GetString(rdr.GetOrdinal("Codigo"));
                            pre.Estado = rdr.GetString(rdr.GetOrdinal("Estado"));
                            pre.Fecha = rdr.GetDateTime(rdr.GetOrdinal("Fecha"));
                            pre.Persona = rdr.GetInt32(rdr.GetOrdinal("Persona"));
                            pre.Usuario = rdr.GetInt32(rdr.GetOrdinal("Usuario"));
                            
                            lista.Add(pre);
                        }
                    }

                }
            }
            catch (SqlException ex)
            {

            }
            return lista;
        }

        

        public int CheckDisponibilidad(int lib, int can)
        {
            try
            {
                string aux = string.Empty;
                using (var cnx = new SqlConnection(ConnectionString))
                {
                    cnx.Open();
                    string cmdText = "usp_DisponibilidadPorLibro";
                    using (var cmd = new SqlCommand(cmdText, cnx))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Parameters.Add(new SqlParameter("@IdLibro", lib));
                        cmd.Parameters.Add(new SqlParameter("@Cantidad", can));

                        try
                        {
                            SqlDataReader dr = cmd.ExecuteReader();
                            while (dr.Read())
                            { aux = dr[0].ToString(); }

                        }
                        catch (SqlException oError)
                        { 

                        }

                        return Convert.ToInt32(aux);
                    }
                }

            }
            catch (SqlException ex)
            {
                return -1;
            }

        }

    }
}
